package interfaces.lunchy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import org.eclipse.swt.widgets.MessageBox;

public class DataBase implements DAO {
	private Connection connection;


	
	public DataBase() throws ClassNotFoundException, SQLException{
		Class.forName("org.postgresql.Driver");
		connection = null;
			connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/bd", "postgres",
					"postgrespass");
		
	}

	@Override
	public void addCategory(String nameCategory) throws Exception {
		try {
			connection.setAutoCommit(false);
			PreparedStatement ps = connection.prepareStatement("INSERT INTO category_menu(category) VALUES (?)");
			ps.setString(1, nameCategory);
			ps.executeUpdate();
			connection.commit();
		} catch (Exception e) {
			connection.rollback();
			throw new Exception();
		}
		Category category = new Category();
		category.setNameCategory(nameCategory);
	}

	private int clearCheckedOrder(Order order) throws SQLException {
		int idClient = 0;
		PreparedStatement ps;
		ps = connection.prepareStatement("SELECT id FROM clients WHERE name = ?");
		Client client = order.getClient();
		ps.setString(1, client.getNameClient());
		ResultSet rs = ps.executeQuery();
		if (rs.next())
			idClient = Integer.parseInt(rs.getString(1));
		ps = connection.prepareStatement("DELETE FROM orders WHERE clients_id = ?");
		ps.setInt(1, idClient);
		ps.executeUpdate();
		return idClient;
	}

	@Override
	public void clearOrder(Order order) throws Exception {
		try {
			connection.setAutoCommit(false);
			PreparedStatement ps = connection.prepareStatement("SELECT id FROM clients WHERE name = ?");
			ps.setString(1, order.getClient().getNameClient());
			ResultSet rs = ps.executeQuery();
			rs.next();
			int idClients = Integer.parseInt(rs.getString(1));
			ps = connection.prepareStatement("DELETE FROM orders WHERE clients_id = ?");
			ps.setInt(1, idClients);
			ps.executeUpdate();
			connection.commit();
		} catch (Exception e) {
			connection.rollback();
			throw new Exception();
		}
		
	}

	@Override
	public boolean deleteCategory(String nameCategory) throws Exception {
		try {
			connection.setAutoCommit(false);
			PreparedStatement ps = connection.prepareStatement("DELETE FROM category_menu WHERE category = ?");
			ps.setString(1, nameCategory);
			ps.executeUpdate();
			connection.commit();
		} catch (Exception e) {
			connection.rollback();
			throw new Exception();
		}
		return true;
	}

	private void deleteRepeatDishes(int idCategory, Dish dish)
			throws SQLException {
		PreparedStatement ps;
		ps = connection.prepareStatement("DELETE FROM dishes WHERE ((category_id = ?) AND (dish = ?))");
		ps.setInt(1, idCategory);
		ps.setString(2, dish.getDish());
		ps.executeUpdate();
	}

	public Category getCategory (String nameCategory) throws Exception{
		Category category = new Category();
		category.setNameCategory(nameCategory);
			connection.setAutoCommit(true);
		PreparedStatement ps = connection.prepareStatement("SELECT dishes.dish, dishes.composition, dishes.weight, dishes.price "+
				"FROM category_menu INNER JOIN dishes ON category_menu.id = dishes.category_id "+
				"WHERE category_menu.category = ?");
		ps.setString(1, nameCategory);
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			List<String> itemsDish = new LinkedList<String>();
			for (int i = 1; i < 5; ++i)
				itemsDish.add(rs.getString(i));
	
			Dish dish = new Dish(itemsDish);
			category.getItemsCategory().add(dish);
		}
		return category;
	}

	private ResultSet getCheckedDishes(ResultSet rsCategoryes, Category category)
			throws SQLException {
		String command;
		PreparedStatement ps;
		command = "SELECT dish, composition, weight, price FROM dishes WHERE ((checked = TRUE) AND (category_id = ?))"; 
		ps = connection.prepareStatement(command);
		ps.setInt(1, Integer.parseInt(rsCategoryes.getString(1)));
		ResultSet rsDishes = ps.executeQuery();
		return rsDishes;
	}

	private int getIdCategory(Category category) throws SQLException {
		PreparedStatement ps = connection.prepareStatement("SELECT id FROM category_menu WHERE category = ?");
		ps.setString(1, category.getNameCategory());
		ResultSet rsIdCategory = ps.executeQuery();
		rsIdCategory.next();
		int idCategory = Integer.parseInt(rsIdCategory.getString(1));
		return idCategory;
	}

	public List<String> getListCategoryMenu() throws Exception{
		List<String> listCategory = new LinkedList<String>();
			connection.setAutoCommit(true);
			PreparedStatement ps = connection.prepareStatement("SELECT category FROM category_menu");
			ResultSet rs = ps.executeQuery();
			while(rs.next())
				listCategory.add(rs.getString(1));
			return listCategory;
	}
		
	private int getMaxId(String command) throws SQLException {
		PreparedStatement ps = connection.prepareStatement(command);
		ResultSet rs = ps.executeQuery();
		int intId = 0;
		rs.next();
		if (rs.getString(1) != null)
			intId = Integer.parseInt(rs.getString(1));
		return intId;
	}

	@Override
	public LunchMenu getMenu() throws Exception {
		LunchMenu menu = new LunchMenu();
		String command = "SELECT id, category FROM category_menu";
		ResultSet rsCategoryes = null;
		try {
			connection.setAutoCommit(false);
			PreparedStatement ps = connection.prepareStatement(command);
			rsCategoryes = ps.executeQuery();
		while (rsCategoryes.next()){
			Category category = new Category();
			category.setNameCategory(rsCategoryes.getString(2));
			ResultSet rsDishes = getCheckedDishes(rsCategoryes, category);
			while(rsDishes.next()){
				List<String> itemsDish = new LinkedList<String>();
				for (int i = 1; i < 5; ++i)
					itemsDish.add(rsDishes.getString(i));
				Dish dish = new Dish(itemsDish);
				category.getItemsCategory().add(dish);
			}
			menu.getItemsMenu().add(category);
		}
		connection.commit();
		return menu;
		} catch (Exception e) {
			connection.rollback();
			throw new Exception();
		}
	}

	@Override
	public String getNameActiveClient() throws Exception {
		connection.setAutoCommit(true);
		String command = "SELECT value FROM properties WHERE key = ?";
		PreparedStatement ps = connection.prepareStatement(command);
		ps.setString(1, CHECKED_CLIENT);
		ResultSet rs = ps.executeQuery();
		String nameClient = null;
		while (rs.next()){
			nameClient = rs.getString(1);
		}
		return nameClient;
	}

	private ResultSet getNameClients(List<String> listClient, int i)
			throws SQLException {
		ResultSet rs;
		PreparedStatement ps = connection.prepareStatement("SELECT name FROM clients WHERE name = ?");
		ps.setString(1, listClient.get(i));
		rs = ps.executeQuery();
		return rs;
	}

	@Override
	public Order getOrder(String nameClient) throws Exception {
		String command = "select d.dish, o.number from  dishes AS d INNER JOIN " +
				"(orders AS o INNER JOIN clients AS c ON o.clients_id = c.id) ON o.dishes_id = d.id" +
				" WHERE c.name = ?";
		Order order = new Order();
		order.getClient().setNameClient(nameClient);
		try {
			connection.setAutoCommit(false);
		ItemsOrder items = setItemsOrder(nameClient, command);
			order.setItems(items);
			connection.commit();
		} catch (Exception e) {
			connection.rollback();
			throw new Exception();
		}
		return order;
	}

	@Override
	public String getProperty(String key) throws Exception {
		String value = null;
		try{
		connection.setAutoCommit(false);
		PreparedStatement ps = null;
		String command = "SELECT value FROM properties WHERE key = ?";
		ps = connection.prepareStatement(command);
		ps.setString(1, key);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			value = rs.getString(1);
		}
		connection.commit();
	    }
	    catch (Exception e){
	    	connection.rollback();
	    	throw new Exception();
	    }
		return value;
	}

	private void insertItemsOrder(Order order, int idClient)
			throws SQLException {
		PreparedStatement ps;
		ps = connection.prepareStatement("INSERT INTO orders(clients_id, dishes_id, number) VALUES(?," +
				"(SELECT id FROM dishes WHERE dish = ?), ?)");
		ps.setInt(1, idClient);
		ItemsOrder items = order.getItems();
		for (int i = 0; i < items.getDishes().size(); ++i)
		{	
			ps.setString(2, items.getDishes().get(i));
			ps.setString(3, items.getNumberDishes().get(i));
			ps.executeUpdate();
		}
	}

	private void insertNewDishes(int idCategory, Dish dish)
			throws SQLException {
		PreparedStatement ps;
		ps = connection.prepareStatement("INSERT INTO dishes(category_id, dish, composition, weight, price, checked)" +
				" VALUES( ?, ?, ?, ?, ?, TRUE)");
		ps.setInt(1, idCategory);
		ps.setString(2, dish.getDish());
		ps.setString(3, dish.getComposition());
		ps.setString(4, dish.getWeight());
		ps.setString(5, dish.getPrice());
		ps.executeUpdate();
	}
	
	public void invertFlagUpdate() throws Exception{
		String flag = null;
		try{
		connection.setAutoCommit(false);
		String command = "SELECT value FROM properties WHERE key = 'FLAG_UPDATE'";
		PreparedStatement ps = connection.prepareStatement(command);
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			flag = rs.getString(1);
		}
		command = "UPDATE properties SET value = ? WHERE key = 'FLAG_UPDATE'";
		ps = connection.prepareStatement(command);
		if (flag.equals("TRUE")){
			ps.setString(1, "FALSE");
		} else {
			ps.setString(1, "TRUE");
		}
		ps.executeUpdate();
		connection.commit();
		}
		catch (Exception e){
			connection.rollback();
			throw new Exception();
		}
	}

	@Override
	public List<String> loadListClient() throws Exception {
		String command = "SELECT name FROM clients";
		try {
			connection.setAutoCommit(false);
		PreparedStatement ps = connection.prepareStatement(command);
		ResultSet rs = null;
			rs = ps.executeQuery();
		List<String> listClient = new ArrayList<String>();
			while (rs.next()){
				listClient.add(rs.getString(1));
			}
			connection.commit();
			return listClient;
		} catch (Exception e) {
			connection.rollback();
			throw new Exception();
		}
	}
	
	private void resetDish() throws SQLException {
		PreparedStatement ps = connection.prepareStatement("UPDATE dishes SET checked = FALSE");
		ps.executeUpdate();
	}
	
	@Override
	public boolean saveListClient(List<String> listClient) throws Exception {
		for (int i = 0; i < listClient.size(); i++){
			ResultSet rs = null;
			try {
				connection.setAutoCommit(false);
				PreparedStatement ps;
				rs = getNameClients(listClient, i);
			if (!rs.next()){
				ps = connection.prepareStatement("INSERT INTO clients(name) VALUES (?)");//insert new client
				ps.setString(1, listClient.get(i));
					ps.executeUpdate();
			}
			connection.commit();
				} catch (Exception e) {
					connection.rollback();
					throw new Exception();
				}
			
		}
		return true;
		
	}

	@Override
	public void saveMenu(LunchMenu menu) throws Exception {
		try {
			connection.setAutoCommit(false);
			resetDish();
		for (Category category : menu.getItemsMenu()){
			int idCategory = getIdCategory(category);
			for(Dish dish : category.getItemsCategory()){
				deleteRepeatDishes(idCategory, dish);
				insertNewDishes(idCategory, dish);
			}
		}
		connection.commit();
		}
		catch (Exception e) {
				connection.rollback();
				throw new Exception();
			}
		}
	
	@Override
	public void saveOrder(Order order) throws Exception{
		try{
			connection.setAutoCommit(false);
			int idClient = clearCheckedOrder(order);
			insertItemsOrder(order, idClient);
			connection.commit();
		}
		catch(Exception e){
			connection.rollback();
			throw new Exception();
		}
		}

	private ItemsOrder setItemsOrder(String nameClient, String command)
			throws SQLException {
		PreparedStatement ps = connection.prepareStatement(command);
		ps.setString(1, nameClient);
		ItemsOrder items = new ItemsOrder();
		ResultSet rs = null;
			rs = ps.executeQuery();
			while (rs.next()){
				items.getDishes().add(rs.getString(1));
				items.getNumberDishes().add(rs.getString(2));
			}
		return items;
	}




	@Override
	public void setNameActiveClient(String nameClient) throws Exception {
		connection.setAutoCommit(true);
		String command = "UPDATE properties SET value = ? WHERE key = ?";
		PreparedStatement ps = connection.prepareStatement(command);
		ps.setString(1, nameClient);
		ps.setString(2, CHECKED_CLIENT);
		ps.executeUpdate();	
	}




	public void setProperty(String key, String value) throws Exception{
		try{
	connection.setAutoCommit(false);
	String command = "UPDATE properties SET value = ? WHERE key = ?";
	PreparedStatement ps = connection.prepareStatement(command);
	ps.setString(1, value);
	ps.setString(2, key);
	ps.executeUpdate();
	connection.commit();
		}
		catch(Exception e){
			connection.rollback();
			throw new Exception();
		}
	}

	@Override
	public void setFlagUpdate(String flagUpdate) throws Exception {
		connection.setAutoCommit(true);
	PreparedStatement ps = connection.prepareStatement("UPDATE properties SET value = ? WHERE key = ?");
	ps.setString(1, flagUpdate);
	ps.setString(2, FLAG_UPDATE);
	ps.executeUpdate();
	}

	@Override
	public String getFlagUpdate() throws Exception {
		String flagUpdate = null;
			connection.setAutoCommit(true);
		PreparedStatement ps = connection.prepareStatement("SELECT value FROM properties WHERE key = ?");
		ps.setString(1, FLAG_UPDATE);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			flagUpdate = rs.getString(1);
		}
		return flagUpdate;
	}

	


}
